/*
 * DBAuthService.java
 *
 * Created on July 2, 2007, 10:57 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.atomojo.app.db;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;
import java.util.UUID;
import org.atomojo.app.auth.AuthCredentials;
import org.atomojo.app.auth.AuthException;
import org.atomojo.app.auth.AuthService;
import org.atomojo.app.auth.User;

/**
 *
 * @author alex
 */
public class DBAuthService extends DBConnectionPool implements AuthService
{
   
   Map<String,User> users;
   
   /** Creates a new instance of DBAuthService */
   public DBAuthService()
   {
      super(null,null);
      users = new TreeMap<String,User>();
   }
   
   static final int STATEMENT_CREATE_GROUP          = 1000;
   static final int STATEMENT_FIND_GROUP_ID         = 1001;
   static final int STATEMENT_DELETE_GROUP_MEMBERS  = 1002;
   static final int STATEMENT_DELETE_GROUP          = 1003;
   static final int STATEMENT_FIND_AUTHOR_ID        = 1004;
   static final int STATEMENT_CREATE_AUTHOR         = 1005;
   static final int STATEMENT_CREATE_GROUP_MEMBER   = 1006;
   static final int STATEMENT_DELETE_GROUP_MEMBER   = 1007;
   static final int STATEMENT_LAST_ID_FROM_AUTHOR   = 1008;
   static final int STATEMENT_AUTHOR_GROUPS         = 1009;
   static final int STATEMENT_AUTHOR_BY_ALIAS       = 1010;
   static final int STATEMENT_ALL_AUTHORS           = 1011;
   static final int STATEMENT_DELETE_AUTHOR         = 1012;
   static final int STATEMENT_UPDATE_AUTHOR         = 1013;
   static final int STATEMENT_UPDATE_PASSWORD       = 1014;
   static final int STATEMENT_AUTHENTICATE          = 1015;
   
   protected void prepare(DBConnection dbConnection)
      throws SQLException
   {
      dbConnection.addStatement(STATEMENT_CREATE_GROUP,"insert into atom.groups (name) values (?)");
      dbConnection.addStatement(STATEMENT_FIND_GROUP_ID,"select id from atom.groups where name=?");
      dbConnection.addStatement(STATEMENT_DELETE_GROUP_MEMBERS,"delete from atom.group_members where group_ref=?");
      dbConnection.addStatement(STATEMENT_DELETE_GROUP,"delete from atom.groups where id=?");
      dbConnection.addStatement(STATEMENT_FIND_AUTHOR_ID,"select id from atom.author where alias=?");
      dbConnection.addStatement(STATEMENT_CREATE_AUTHOR, "insert into atom.author (alias,uuid,name,email,password_md5,created,valid) values (?,?,?,?,?,?,?)");
      dbConnection.addStatement(STATEMENT_CREATE_GROUP_MEMBER, "insert into atom.group_members (author_ref,group_ref) values (?,?)");
      dbConnection.addStatement(STATEMENT_DELETE_GROUP_MEMBER, "delete from atom.group_members where group_ref=? and author_ref=?");
      dbConnection.addStatement(STATEMENT_LAST_ID_FROM_AUTHOR,"select identity_val_local() from atom.author");
      dbConnection.addStatement(STATEMENT_AUTHOR_GROUPS,"select name from atom.groups,atom.group_members where atom.group_members.author_ref=? and atom.group_members.group_ref=atom.groups.id");
      dbConnection.addStatement(STATEMENT_AUTHOR_BY_ALIAS,"select id,uuid,name,email,created,valid from atom.author where alias=?");
      dbConnection.addStatement(STATEMENT_ALL_AUTHORS,"select id,alias,uuid,name,email,created,valid from atom.author");
      dbConnection.addStatement(STATEMENT_DELETE_AUTHOR,"delete from atom.author where alias=?");
      dbConnection.addStatement(STATEMENT_UPDATE_AUTHOR,"update atom.author set name=?,email=? where alias=?");
      dbConnection.addStatement(STATEMENT_UPDATE_PASSWORD, "update atom.author set password_md5=? where alias=?");
      dbConnection.addStatement(STATEMENT_AUTHENTICATE,"select id,uuid,name,email from atom.author where alias=? and password_md5=?");
   }
   
   public void init(Properties props)
   {
      String name = props.getProperty("database");
      String path = props.getProperty("dir");
      setConnection(name,new File(path));
   }
   
   public void release(ResultSet r)
      throws SQLException
   {
      if (r!=null) {
         r.close();
      }
   }
   
   public void release(PreparedStatement statement)
      throws SQLException
   {
      if (statement!=null) {
         statement.clearParameters();
      }
   }
   
   public boolean createGroup(AuthCredentials cred,String name)
      throws AuthException
   {
      try {
         PreparedStatement s = null;
         DBConnection dbConnection = getConnection();
         try {
            s = dbConnection.getStatement(STATEMENT_CREATE_GROUP);
            s.setString(1,name);
            int count = s.executeUpdate();
            return count==1;
         } catch (SQLException ex) {
            throw new AuthException("Cannot create group "+name+" due to SQL error.",ex);
         } finally {
            release(s);
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }

   public boolean deleteGroup(AuthCredentials cred,String name)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
            if (groupId<0) {
               throw new AuthException("Group "+name+" cannot be found.");
            }
            PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_GROUP_MEMBERS);
            s.setInt(1,groupId);
            s.executeUpdate();
            release(s);
            s = dbConnection.getStatement(STATEMENT_DELETE_GROUP);
            s.setInt(1,groupId);
            int count = s.executeUpdate();
            release(s);
            return count>0;
         } catch (SQLException ex) {
            throw new AuthException("Cannot delete group "+name,ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }

   }

   public boolean addUserToGroup(AuthCredentials cred,String alias,String name)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            int userId = lookupInternalId(STATEMENT_FIND_AUTHOR_ID,alias);
            int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
            if (groupId<0) {
               throw new SQLException("Group "+name+" cannot be found.");
            }
            PreparedStatement s = dbConnection.getStatement(STATEMENT_CREATE_GROUP_MEMBER);
            s.setInt(1,userId);
            s.setInt(2,groupId);
            int count = s.executeUpdate();
            release(s);
            if (count!=1) {
               return false;
            }
         } catch (SQLException ex) {
            throw new AuthException("Cannot add user "+alias+" to group "+name+" due to SQL error.",ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
      return true;
   }

   public boolean removeUserFromGroup(AuthCredentials cred,String alias,String name)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            int userId = lookupInternalId(STATEMENT_FIND_AUTHOR_ID,alias);
            if (userId<0) {
               throw new AuthException("User "+alias+" cannot be found.");
            }
            int groupId = lookupInternalId(STATEMENT_FIND_GROUP_ID,name);
            if (groupId<0) {
               throw new AuthException("Group "+name+" cannot be found.");
            }
            PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_GROUP_MEMBER);
            s.setInt(1,groupId);
            s.setInt(2,userId);
            int count = s.executeUpdate();
            release(s);
            return count>0;
         } catch (SQLException ex) {
            throw new AuthException("Cannot delete user "+alias+" from group "+name,ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }

   }

   public User createUser(AuthCredentials auth,String alias,String name,String email,String password) 
      throws AuthException
   {
      UUID uid = UUID.randomUUID();
      try {
         String md5Password = User.md5Password(password);

         DBConnection dbConnection = getConnection();
         try {
            PreparedStatement s = dbConnection.getStatement(STATEMENT_CREATE_AUTHOR);
            Timestamp tstamp = new Timestamp((new Date()).getTime());
            s.setString(1, alias);
            s.setString(2,uid.toString());
            if (name==null) {
               s.setNull(3,Types.VARCHAR);
            } else {
               s.setString(3,name);
            }
            if (email==null) {
               s.setNull(4,Types.VARCHAR);
            } else {
               s.setString(4,email);
            }
            s.setString(5,md5Password);
            s.setTimestamp(6,tstamp);
            s.setBoolean(7,true);
            int count = s.executeUpdate();
            if (count!=1) {
               throw new SQLException("Cannot insert user "+alias+" into DB.");
            }
            release(s);
            s = dbConnection.getStatement(STATEMENT_LAST_ID_FROM_AUTHOR);
            ResultSet r = s.executeQuery();
            int id = -1;
            if (r.next()) {
               id = r.getInt(1);
            }
            release(r);
            release(s);
            
            List<String> empty = Collections.emptyList();
            User u = new User(alias,uid,name,email,empty);
            synchronized (users) {
               users.put(alias,u);
            }
            return u;
         } catch (SQLException ex) {
            throw new AuthException("Cannot create user "+alias+" due to SQL error.",ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      } catch (java.security.NoSuchAlgorithmException ex) {
         throw new AuthException(ex.getMessage());
      }

   }

   protected List<String> getGroups(int id)
      throws SQLException
   {
      DBConnection dbConnection = getConnection();
      List<String> names = new ArrayList<String>();
      try {
         PreparedStatement s = dbConnection.getStatement(STATEMENT_AUTHOR_GROUPS);
         s.setInt(1,id);
         ResultSet r = s.executeQuery();
         while (r.next()) {
            names.add(r.getString(1));
         }
         release(r);
         return names;
      } finally {
         release(dbConnection);
      }

   }

   public User getUser(AuthCredentials cred,String alias)
      throws AuthException
   {
      User u = users.get(alias);
      if (u==null) {
         try {
            DBConnection dbConnection = getConnection();
            try {
               PreparedStatement s = dbConnection.getStatement(STATEMENT_AUTHOR_BY_ALIAS);
               s.setString(1,alias);
               ResultSet r = s.executeQuery();
               if (r.next()) {
                  int id = r.getInt(1);
                  u = new User(alias,UUID.fromString(r.getString(2)),r.getString(3),r.getString(4),getGroups(id));
                  synchronized (users) {
                     users.put(alias,u);
                  }
               }
               release(r);
               release(s);
            } catch (SQLException ex) {
               throw new AuthException("Cannot get user "+alias+" due to SQL error.",ex);
            } finally {
               release(dbConnection);
            }
         } catch (SQLException ex) {
            throw new AuthException("Cannot get database connection.",ex);
         }
      }
      return u;

   }

   public Iterator<User> getUsers(AuthCredentials cred) 
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            PreparedStatement s = dbConnection.getStatement(STATEMENT_ALL_AUTHORS);
            return new DBIterator<User>(s.executeQuery(),new ResultConstructor<User>() {
               public User newInstance(ResultSet r) 
                  throws SQLException
               {
                  int id = r.getInt(1);
                  String alias = r.getString(2);
                  User u = users.get(alias);
                  if (u==null) {
                     u = new User(alias,UUID.fromString(r.getString(3)),r.getString(4),r.getString(5),getGroups(id));
                     synchronized (users) {
                        users.put(u.getAlias(),u);
                     }
                  }
                  return u;
               }

            },this,dbConnection);
         } catch (SQLException ex) {
            release(dbConnection);
            throw new AuthException("Cannot execute queries connection.",ex);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }

   public boolean deleteUser(AuthCredentials cred,String alias)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            PreparedStatement s = dbConnection.getStatement(STATEMENT_DELETE_AUTHOR);
            s.setString(1,alias);
            int count = s.executeUpdate();
            release(s);
            synchronized (users) {
               users.remove(alias);
            }
            return count>0;
         } catch (SQLException ex) {
            throw new AuthException("Cannot delete user "+alias+" from the database.",ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }

   public boolean updateUser(AuthCredentials cred,String alias,String name,String email)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            PreparedStatement s = dbConnection.getStatement(STATEMENT_UPDATE_AUTHOR);
            if (name==null) {
               s.setNull(1, Types.VARCHAR);
            } else {
               s.setString(1,name);
            }
            if (email==null) {
               s.setNull(2, Types.VARCHAR);
            } else {
               s.setString(2,email);
            }
            s.setString(3,alias);
            int count = s.executeUpdate();
            release(s);
            synchronized (users) {
               users.remove(alias);
            }
            return count>0;
         } catch (SQLException ex) {
            throw new AuthException("Cannot update user "+alias+" in the database.",ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }

   public boolean setPassword(AuthCredentials cred,String alias,String password)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         try {
            PreparedStatement s = dbConnection.getStatement(STATEMENT_UPDATE_PASSWORD);
            s.setString(1, User.md5Password(password));
            s.setString(2,alias);
            int count = s.executeUpdate();
            release(s);
            return count>0;
         } catch (java.security.NoSuchAlgorithmException ex) {
            throw new AuthException(ex.getMessage());
         } catch (SQLException ex) {
            throw new AuthException("Cannot update user "+alias+" in the database.",ex);
         } finally {
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }

   public User authenticate(String alias,String password)
      throws AuthException
   {
      try {
         DBConnection dbConnection = getConnection();
         PreparedStatement s = null;
         ResultSet r = null;
         try {
            s = dbConnection.getStatement(STATEMENT_AUTHENTICATE);
            s.setString(1,alias);
            s.setString(2,User.md5Password(password));
            
            r = s.executeQuery();
            User u = null;
            if (r.next()) {
               int id = r.getInt(1);
               UUID uuid = UUID.fromString(r.getString(2));
               String name = r.getString(3);
               u = users.get(alias);
               if (u==null) {
                  u = new User(alias,uuid,name,r.getString(4),getGroups(id));
                  synchronized (users) {
                     users.put(u.getAlias(),u);
                  }
               }
            }
            return u;
         } catch (java.security.NoSuchAlgorithmException ex) {
            throw new AuthException(ex.getMessage());
         } catch (SQLException ex) {
            throw new AuthException("Cannot get user "+alias+" from database.",ex);
         } finally {
            release(r);
            release(s);
            release(dbConnection);
         }
      } catch (SQLException ex) {
         throw new AuthException("Cannot get database connection.",ex);
      }
   }
   
   public User verifySession(String session)
      throws AuthException
   {
      return null;
   }


   protected int lookupInternalId(int statementId,String value)
      throws SQLException
   {
      PreparedStatement s = null;
      ResultSet r = null;
      DBConnection dbConnection = getConnection();
      try {
         s = dbConnection.getStatement(statementId);
         s.setString(1,value);
         r = s.executeQuery();
         if (r.next()) {
            return r.getInt(1);
         }
         return -1;
      } finally {
         release(r);
         release(s);
         release(dbConnection);
      }
      
   }
   
}
